#股票数据源  pip install lxml pandas requests bs4  解决StringIO的问题 tushare pip install "pandas<0.25.0"
import sys

import tushare as ts
import  pandas as pd
#数据库依赖包 安装用命令pip install  pymysql /pip install MySQLdb（Python2）
import pymysql
import requests
import time
#时间格式化方法
import datetime
from sqlalchemy import create_engine
#判断编码格式的依赖--好像不是很好用
import chardet
#获取code信息
#请求数据
#解析数据
#入库

#step1 获取code信息
def getCode(type='股票'):
    # step1获取数据(从数据库中获取)
    db = pymysql.Connect('localhost', 'root', 'abcde@124', 'test')
    # 创建游标
    cursor = db.cursor()
    # 要执行的sql
    if(type!='股票'):
        sql = 'select distinct b.code from invest_base b where b.category not in (\'股票\',\'存款\') and (b.isdelete=\'0\' or b.isdelete=\'1\' or b.isdelete=\'2\')'
    else:
        sql = 'select distinct b.code from invest_base b where b.category=\''+type+'\'  and (b.isdelete=\'0\' or b.isdelete=\'1\'or b.isdelete=\'2\')'
    cursor.execute(sql)
    info = cursor.fetchall()
    codelist = []
    for code in info:
        codelist.append(code[0])
    #    print(len(codelist))
    #   print(codelist)#第一个是数据的条数,
    # 关闭游标
    cursor.close()
    # 关闭数据库
    db.close()
    return codelist
#step2 逐个获取股票数据
def getDataStock(codelist):
    #print('getDataStock')
    for code in codelist:
       # print(code)
        df = ts.get_hist_data(code)
       #保存备份
        df.to_csv('d:/data/stock/'+code+'.csv')
    return df
#step3 解析数据
def parseDataStock(code):
        db = pymysql.Connect('localhost', 'root', 'abcde@124', 'test')
        # 创建游标
        cursor = db.cursor()
        sql = 'select max(nowdate) from invest_daily d where d.code=%s'
        cursor.execute(sql,code)
        dates = cursor.fetchone()
        if(dates[0]==None):
            mdate='1990-01-01'
        else:
            mdate=dates[0]
        db.commit()
        # 关闭游标
        cursor.close()
        # 关闭数据库
        db.close()
        path = 'd:/data/stock/'+code+'.csv'
        df = pd.read_csv(path, encoding="gbk")
        df = df[df.date >str(mdate)]
        df['code']=code
        df['nowdate']=df['date']
        df['nowprice']=df['close']
        df['nowrate']=df['p_change']
        dbs = df[['code', 'nowdate', 'nowprice', 'nowrate']]
        engine = create_engine("mysql+pymysql://{}:{}@{}/{}".format('root', 'abcde@124', 'localhost:3306', 'test'))
        dbs.to_sql(name='invest_daily', con=engine, if_exists='append', index=False)
        #print('获取所有列字段信息',df.columns)
       # print('获取最大值', df.date.max())
       #print('获取收盘价格', dfv.loc[0])
        #print('涨跌幅', dfchange.loc[0])
       # return tup


#step2.1从网络获取数据
def getDataFund(codelist):
    #print('getDataFund')
    for code in codelist:
        r0 = requests.get('http://fund.eastmoney.com/pingzhongdata/' + code + '.js')
        ori = r0.text
        #  print(code,'netinfo=',ori)
        path = 'd:/data/fund/' + code + '.txt'
        with  open(path, "w+", encoding='utf-8')  as fo:
            fo.write(ori)
        fo.close()
#step2.2处理成csv文件
def createCSV(code):
        #print('createCSV start!!!'+code)
        f = open('d:/data/fund/' + code + '.txt', encoding='utf-8')
        content = f.read()  # 使用loads()方法，需要先读文件
        f.close()
        line = content.split("Data_netWorthTrend =")[1].split(";/*累计净值走势*/var Data_ACWorthTrend")[0]
        unitList = list(eval(line))
        # 获取累计净值 [时间，累计净值]
        line = content.split(";/*累计净值走势*/var Data_ACWorthTrend =")[1].split(";/*累计收益率走势*/var Data_grandTotal =")[0]
        sumList = list(eval(line))
        # 获取同类排名百分比
        line = content.split(";/*同类排名百分比*/var Data_rateInSimilarPersent=")[1].split(";/*规模变动 mom-较上期环比*/var")[0]
        perList = list(eval(line))
        # print(len(perList),"----", formatTime(perList[0][0]),perList[0][1])
        path = 'd:/data/fund/' + code + '.csv'
        #    print(unitList[0])
        with  open(path, "w")  as fo:
            fo.write('时间,单位净值,每日涨跌,累计净值\n')
            for i in range(0, len(unitList)):  # len(unitList)
                fo.write(formatTime(unitList[i]['x']) + "," + str(unitList[i]['y']) + "," + str(
                    unitList[i]['equityReturn']) + "," + str(sumList[i][1]) + "\n")
        fo.close()
        fundbasic = pd.read_csv(path, encoding='gbk')
        perdf = pd.DataFrame(perList, columns=['时间', '同类排名百分比'])
        perdf['时间'] = perdf['时间'].apply(lambda x: formatTime(x))
        # https://blog.csdn.net/qq_41664845/article/details/80047109
        df = pd.merge(fundbasic, perdf, on='时间', how='outer')  # 取并集
        df = df.set_index('时间')
        df.to_csv(path)
        return df
def parseDataFund(code):
        db = pymysql.Connect('localhost', 'root', 'abcde@124', 'test')
        # 创建游标
        cursor = db.cursor()
        sql = 'select max(nowdate) from invest_daily d where d.code=%s'
        cursor.execute(sql,code)
        dates = cursor.fetchone()
        if(dates[0]==None):
            mdate='1990-01-01'
        else:
            mdate=dates[0]
        db.commit()
        # 关闭游标
        cursor.close()
        # 关闭数据库
        db.close()
        path = 'd:/data/fund/' + code + '.csv'
        df = pd.read_csv(path)
        df = df[df.时间 >str(mdate)]
        df['code']=code
        df['nowdate']=df['时间']
        df['nowprice']=df['单位净值']
        df['nowrate']=df['每日涨跌']
        dbs = df[['code', 'nowdate', 'nowprice', 'nowrate']]
        engine = create_engine("mysql+pymysql://{}:{}@{}/{}".format('root', 'abcde@124', 'localhost:3306', 'test'))
        dbs.to_sql(name='invest_daily', con=engine, if_exists='append', index=False)
#公共方法
#---工具类---#
def formatTime(oristr):
    ori = float(oristr) / 1000
    time_tuple = time.localtime(ori)
    t1 = time.strftime("%Y-%m-%d", time_tuple)
    return str(t1)
#获取前1天的时间 "%Y-%m-%d"
def getTime(mytime,num,format):
    stdtime = datetime.datetime.strptime(mytime, format)
    newtime = (stdtime + datetime.timedelta(days=num)).strftime(format)
    return newtime
def test2():
    print('[',len(sys.argv),']')
def test(start='2020-01-28',end='2020-01-29'):
    current=start
    while current<=end:
        print('current=' + current)
        current=getTime(current, 1, '%Y-%m-%d')

def start():
    codeStocklist = getCode('股票')
    codeFundlist = getCode('基金')
    #在线请求
    if len(sys.argv)>1:
        getDataFund(codeFundlist)
        getDataStock(codeStocklist)
        print('在线',len(sys.argv))
    else:
        print('离线')
    for code in codeStocklist:
        parseDataStock(code)
        print('stock ',code)
    for code in codeFundlist:
        createCSV(code)
        parseDataFund(code)
        print('fund ', code)
start()

